﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using C1.Win.C1FlexGrid;
using System.Collections;
using BHXH_NT.Properties;


namespace BHXH_NT
{
    public partial class frmCreateBHYT : Form
    {
        private string connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString1;
        private string connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
        private string Condition,strMdvikcb,strMadt,strMatinh,strMakhoikcb;
        String[] lsDonvi, lsMadvikcb, lsMadt, lsMatinh,lsMakhoikcb;
        Boolean FlagGiahan;
        Random rnd = new Random();
        List<string> arrBV = new List<string>();
        public frmCreateBHYT()
        {
            InitializeComponent();
        }
        #region "Private Method"
        private void GetFlexGeneral(object sender)
        {
            
            string queryString;
            //truong hop BD gan mac dinh tungay = 01/10/2011 denngay = 30/09/2012
            if (txtMakhoi.Text.ToUpper() == "BD")
            {
                //dtngay.Value = "01/10/" + DateTime.Now.Year.ToString();
                //dtDenngay.Value = "30/09/" + Convert.ToInt16(DateTime.Now.Year + 1);

                dtngay.Value = "01/01/" + DateTime.Now.Year.ToString();
                dtDenngay.Value = "30/09/" + DateTime.Now.Year.ToString();
            }
            if (sender != null) 
            {

                dtngay.Value = "01/" + DateTime.Now.Month + "/" + Convert.ToInt16(DateTime.Now.Year);
                dtDenngay.Value = "31/12/" + Convert.ToInt16(DateTime.Now.Year);
            }
            else//gian han hang nam
            {
                dtngay.Value = "01/01/" + Convert.ToInt16(DateTime.Now.Year + 1);
                dtDenngay.Value = "31/12/" + Convert.ToInt16(DateTime.Now.Year + 1);
               
            }
           
            if (FlagGiahan == true) // gia han the
            {
                queryString = "select * from DMDONVI where MAKHOI = '" + txtMakhoi.Text + "' AND MADVI NOT IN "
                    + " (SELECT MADVI FROM " + Settings.Default.DBName_Sothe + ".DBO.INTHE_HDR WHERE "
                    + " denngay >= '" + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "/" + Convert.ToDateTime(dtngay.Value).Day + "/" + Convert.ToDateTime(dtngay.Value).Year + "'"
                    + " and Denngay <='" + string.Format("{0:00}", Convert.ToDateTime(dtDenngay.Value).Month) + "/" + Convert.ToDateTime(dtDenngay.Value).Day + "/" + Convert.ToDateTime(dtDenngay.Value).Year + "'"
                    + " AND Loai='1') Order by Madvi";
            }
            else // tang moi the
            {
                queryString = "select *, '' as KEYGH from DMDONVI a INNER JOIN " + Settings.Default.DBName_Sothe + ".DBO.INTHE_HDR b "
                + " ON a.Madvi = b.Madvi where MAKHOI = '" + txtMakhoi.Text + "' AND Loai = '1' and "
                + " Denngay >= '" + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "/" + Convert.ToDateTime(dtngay.Value).Day + "/" + Convert.ToDateTime(dtngay.Value).Year + "' Order by a.Madvi";
            }
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxGeneral.Rows.Count = 1;

                while (reader.Read())
                {
                    FlxGeneral.Rows.Count = FlxGeneral.Rows.Count + 1;
                    FlxGeneral.Rows[i]["STT"] = i;
                    FlxGeneral.Rows[i]["Check"] = 0;
                    FlxGeneral.Rows[i]["MADVI"] = reader["MADVI"];
                    FlxGeneral.Rows[i]["TENDVI"] = reader["TENDVI"];
                    FlxGeneral.Rows[i]["KEYSL"] = reader["MADVI"].ToString().Substring(2, 4);
                    FlxGeneral.Rows[i]["MADVIKCB"] = reader["MADVIKCB"];
                    FlxGeneral.Rows[i]["MA_DT"] = reader["MA_DT"];
                    FlxGeneral.Rows[i]["MA_TINH"] = reader["MA_TINH"];
                    FlxGeneral.Rows[i]["MAKHOIKCB"] = reader["MAKHOIKCB"];
                    if (FlagGiahan == false)
                    {
                        FlxGeneral.Rows[i]["KEYGH"] = reader["KEYGH"];
                    }
                    i++;
                }
                reader.Close();

            }
        }
        #endregion

        #region "Event Method"
        /// <summary>
        /// gan cac tham so mac dinh
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frmCreateBHYT_Load(object sender, EventArgs e)
        {
            dtngay.Value = "01" + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();
            dtDenngay.Value = "31/12/" + DateTime.Now.Year.ToString();
            cboInthe.SelectedIndex = 0;
            GetFlexGeneral(sender);
            GetBV();
        }
        private void GetBV()
        {
            
            string queryString = "select * from dbo.DMBV where MA_TINH = 56 and KHONGSD = 0";
            using (SqlConnection connection =
                  new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    arrBV.Add(reader["MA_BV"].ToString());
                }
                reader.Close();
               

            }
        }
        /// <summary>
        /// lay cac nhan vien chua tao the theo cac ma dvi da chon
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSolieu_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            string queryString;
            Condition = "";
            int stt = 1;
            strMadt = strMatinh = strMdvikcb = strMakhoikcb = "";
            FlxDetail.Rows.Count = 1;
            DateTime dt = new DateTime(2009, 10, 1);
            FlxGeneral.Sort(SortFlags.Ascending, 2);
            FlxGeneral.Sort(SortFlags.Descending, 1);
            btnThe.Enabled = false;
            btnChapnhan.Enabled = false;
            
            for (int i = 1; i < FlxGeneral.Rows.Count; i++)
            {
                if (FlxGeneral.Rows[i]["Check"].ToString() == "True")
                {
                    //tao String gom cac don vi duoc chon
                    Condition = Condition + "'" + FlxGeneral.Rows[i]["MADVI"] + "',";
                    // kiem tra ma the da duoc tao chua
                    if (FlxGeneral.Rows[i]["MA_DT"].ToString().Trim() != "") { strMadt = strMadt + FlxGeneral.Rows[i]["MA_DT"] + ","; }
                    else { MessageBox.Show("NhËp m· ®èi t­îng"); return; }
                                        
                    if (FlxGeneral.Rows[i]["MADVIKCB"].ToString().Trim() != "") { strMdvikcb = strMdvikcb + FlxGeneral.Rows[i]["MADVIKCB"] + ","; }
                    else { MessageBox.Show("nhËp m· ®¬n vÞ  kcb"); return; }

                    if (FlxGeneral.Rows[i]["MAKHOIKCB"].ToString().Trim() != "") { strMakhoikcb = strMakhoikcb + FlxGeneral.Rows[i]["MAKHOIKCB"] + ","; }
                    else { MessageBox.Show("nhËp m· khèi kcb"); return; }
                }
                else
                {
                    break;
                }
            }
            if (Condition == "") { return; }
            //tao cac chuoi phuc vu cho luc tao so KCB
            Condition = Condition.Substring(0, Condition.Length - 1);
            if (strMadt != null) { strMadt = strMadt.Substring(0, strMadt.Length - 1); };
            if (strMdvikcb != null) { strMdvikcb = strMdvikcb.Substring(0, strMdvikcb.Length - 1); };
            if (strMakhoikcb != null) { strMakhoikcb = strMakhoikcb.Substring(0, strMakhoikcb.Length - 1); };

            if (FlagGiahan == false)// truong hop tang moi
            {
                if (txtMakhoi.Text.ToUpper() == "BE")//truong hop tre em
                {
                    queryString = @"SELECT 0 As Chon,a.Madvi, b.Maqt, a.Sobhxh, c.Ho, c.Ten, c.Sokcb , c.Ngaysinh, c.Gioitinh,"
                       + " CASE WHEN d.InTenDvi<>0 THEN d.Tentat ELSE c.DiaChi END As DiaChi, "
                       + " case when ltrim(rtrim(c.Ma_Tinh)) = '' then '" + Properties.Settings.Default.MaTinh + "' else c.Ma_Tinh end as Ma_Tinh, c.Ma_bv, b.Pa, c.MaPB,  d.MakhoiKCB As MaDt, c.Tungay As NgayQD,"
                       + " c.Sothang,d.MAKHOIKCB, d.MADVIKCB, "
                       + " CASE WHEN b.Pa='TS' THEN b.Hsl_Dk+b.Pc1_Dk+b.Pc2_Dk+b.Pc3_Dk+b.Pc4_Dk+b.Pc5_Dk ELSE b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5 END As HSL, "
                       + " CASE WHEN b.Pa='TS' THEN b.Ml_Dk+b.Mlyt_Dk ELSE b.Ml+b.Mlyt END As ML  "
                       + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh, DMDONVI d , "
                       + " (SELECT Madvi,Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt "
                       + " FROM HSC47_DTL WHERE Madvi in (" + Condition + ")  AND LEFT(Maqt,6)=Denthang "
                       + " AND LEFT(Maqt,6)<=" + Convert.ToDateTime(dtngay.Value).Year + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "  AND Ma_Nte='VND' AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
                       + " GROUP BY Madvi,Sobhxh, Ma_Nte, Loaidt) a  WHERE a.Sobhxh=b.Sobhxh AND a.Ma_Nte=b.Ma_Nte "
                       + " AND a.Loaidt=b.Loaidt AND a.Maqt=b.Maqt AND b.Madvi= a.Madvi "
                       + " AND LEFT(b.Maqt,6)=b.Denthang AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0  and b.Madvi=d.Madvi  "
                       + " AND (b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 OR b.Pa='TS' )  "
                       + " and a.Sobhxh not in (SELECT DISTINCT sobhxh FROM " + Settings.Default.DBName_Sothe + ".DBO.INTHE_DTL   "
                       + " WHERE  MAdvi in (" + Condition + ") AND Hethan = 0 )"
                       + " AND left(a.maqt,4)= " + "20" + ModuleBHXH.GetYear()
                       + " ORDER BY a.Madvi,c.SoKCB";
                }
                else //TRUONG HOP KHAC
                {
                    queryString = @"SELECT 0 As Chon,a.Madvi, CASE WHEN b.Pa='TS' then b.Maqt + ' - TS'  else  b.Maqt end as Maqt, a.Sobhxh, c.Ho, c.Ten, c.Sokcb , c.Ngaysinh, c.Gioitinh,"
                    + " CASE WHEN d.InTenDvi<>0 THEN d.Tentat ELSE c.DiaChi END As DiaChi, "
                    + " case when ltrim(rtrim(c.Ma_Tinh)) = '' then '" + Properties.Settings.Default.MaTinh + "' else c.Ma_Tinh end as Ma_Tinh, c.Ma_bv, b.Pa, c.MaPB,  d.MakhoiKCB As MaDt, c.Tungay As NgayQD,"
                    + " c.Sothang,d.MAKHOIKCB, d.MADVIKCB, "
                    + " CASE WHEN b.Pa='TS' THEN b.Hsl_Dk+b.Pc1_Dk+b.Pc2_Dk+b.Pc3_Dk+b.Pc4_Dk+b.Pc5_Dk ELSE b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5 END As HSL, "
                    + " CASE WHEN b.Pa='TS' THEN b.Ml_Dk+b.Mlyt_Dk ELSE b.Ml+b.Mlyt END As ML  "
                    + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh , DMDONVI d , "
                    + " (SELECT Madvi,Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt "
                    + " FROM HSC47_DTL WHERE Madvi in (" + Condition + ")  AND LEFT(Maqt,6)=Denthang "
                    + " AND LEFT(Maqt,6)<=" + Convert.ToDateTime(dtngay.Value).Year + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "  AND Ma_Nte='VND' AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
                    + " GROUP BY Madvi,Sobhxh, Ma_Nte, Loaidt) a  WHERE a.Sobhxh=b.Sobhxh AND a.Ma_Nte=b.Ma_Nte "
                    + " AND a.Loaidt=b.Loaidt AND a.Maqt=b.Maqt AND b.Madvi= a.Madvi "
                    + " AND LEFT(b.Maqt,6)=b.Denthang AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0  and b.Madvi=d.Madvi  "
                    + " AND (b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 OR b.Pa='TS' )  "
                    + " and a.Sobhxh not in (SELECT DISTINCT sobhxh FROM " + Settings.Default.DBName_Sothe + ".DBO.INTHE_DTL   "
                    + " WHERE  KeyGH IN (select KeyGH FROM " + Settings.Default.DBName_Sothe + ".DBO.INTHE_HDR WHERE MAdvi in (" + Condition + ") "
                    + " AND Loai = '1' and Denngay >= '" + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "/" + Convert.ToDateTime(dtngay.Value).Day + "/" + Convert.ToDateTime(dtngay.Value).Year + "' )   "
                    + " AND Hethan = 0 AND Denngay >= '" + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "/" + Convert.ToDateTime(dtngay.Value).Day + "/" + Convert.ToDateTime(dtngay.Value).Year + "' )"
                    + " ORDER BY a.Madvi,c.SoKCB";
                }


            }
            else //TRUONG HOP GIA HAN
            {

                queryString = @"SELECT 0 As Chon,a.Madvi,  CASE WHEN b.Pa='TS' then b.Maqt + ' - TS'  else  b.Maqt end as Maqt, a.Sobhxh, c.Ho, c.Ten, c.Sokcb, c.Ngaysinh, c.Gioitinh,"
                       + " CASE WHEN d.InTenDvi<>0 THEN d.Tentat ELSE c.DiaChi END As DiaChi, "
                       + " case when ltrim(rtrim(c.Ma_Tinh)) = '' then '" + Properties.Settings.Default.MaTinh + "' else c.Ma_Tinh end as Ma_Tinh , c.Ma_bv, b.Pa, c.MaPB,  d.MakhoiKCB As MaDt, c.Tungay As NgayQD,"
                       + " c.Sothang,d.MAKHOIKCB, d.MADVIKCB, "
                       + " CASE WHEN b.Pa='TS' THEN b.Hsl_Dk+b.Pc1_Dk+b.Pc2_Dk+b.Pc3_Dk+b.Pc4_Dk+b.Pc5_Dk ELSE b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5 END As HSL, " 
                       + " CASE WHEN b.Pa='TS' THEN b.Ml_Dk+b.Mlyt_Dk ELSE b.Ml+b.Mlyt END As ML  "
                       + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh, DMDONVI d , "
                       + " (SELECT Madvi,Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt "
                       + " FROM HSC47_DTL WHERE Madvi in (" + Condition + ")  AND LEFT(Maqt,6)=Denthang "
                       + " AND LEFT(Maqt,6)<=" + Convert.ToDateTime(dtngay.Value).Year + string.Format("{0:00}", string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month)) + "  AND Ma_Nte='VND' AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
                       + " GROUP BY Madvi,Sobhxh, Ma_Nte, Loaidt) a  WHERE a.Sobhxh=b.Sobhxh AND a.Ma_Nte=b.Ma_Nte "
                       + " AND a.Loaidt=b.Loaidt AND a.Maqt=b.Maqt AND b.Madvi= a.Madvi "
                       + " AND LEFT(b.Maqt,6)=b.Denthang AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and b.Madvi=d.Madvi  "
                       + " AND (b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 OR b.Pa='TS' )  "
                       + " ORDER BY a.Madvi,c.SoKCB";

            }

            using (SqlConnection connection =
                  new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxDetail.Tree.Column = 4;
                while (reader.Read())
                {
                    if (FlxDetail.Rows[i - 1]["Madvi"].ToString() != reader["Madvi"].ToString())
                    {
                        FlxDetail.Rows.InsertNode(i, 1);
                        stt = 1;

                    }
                    else
                    {
                        FlxDetail.Rows.InsertNode(i, 2);
                    }
                  
                    FlxDetail.SetCellCheck(i, 4, CheckEnum.Checked);
                    FlxDetail.Rows[i]["STT1"] = i;
                    FlxDetail.Rows[i]["Check"] = 1;
                    FlxDetail.Rows[i]["STT"] = stt;
                    FlxDetail.Rows[i]["Madvi"] = reader["Madvi"];
                    FlxDetail.Rows[i]["Sobhxh"] = reader["Sobhxh"];
                    if (reader["SoKCB"].ToString().ToString() != "" && reader["SoKCB"].ToString().Substring(0, 2) + reader["SoKCB"].ToString().Substring(5, 5) != reader["MAKHOIKCB"].ToString().Trim() + reader["MADVIKCB"].ToString().Trim())
                    {
                        FlxDetail.Rows[i]["SoKCB"] = "";
                    }
                    else
                    {
                        FlxDetail.Rows[i]["SoKCB"] = reader["SoKCB"];
                    }
                    FlxDetail.Rows[i]["Ho"] = reader["Ho"];
                    FlxDetail.Rows[i]["Ten"] = reader["Ten"];
                    FlxDetail.Rows[i]["NgaySinh"] = reader["NgaySinh"];
                    if (txtMakhoi.Text.ToUpper() == "BE") //TRUONG HOP TRE EM TINH LAI TU NGAY DEN NGAY
                    {

                        //if (DateTime.Compare(Convert.ToDateTime(FlxDetail.Rows[i]["NGAYSINH"]), dt) > 0)
                        //{
                        //    FlxDetail.Rows[i]["TUNGAY"] = FlxDetail.Rows[i]["NGAYSINH"];
                        //}
                        //else
                        //{
                        //    FlxDetail.Rows[i]["TUNGAY"] = dt;
                        //}
                        FlxDetail.Rows[i]["TuNgay"] = dtngay.Text;
                        FlxDetail.Rows[i]["DENNGAY"] = Convert.ToDateTime(FlxDetail.Rows[i]["NGAYSINH"]).AddYears(6).AddDays(-1);
                    }
                    else
                    {
                        FlxDetail.Rows[i]["TuNgay"] = dtngay.Text;
                        FlxDetail.Rows[i]["DenNgay"] = dtDenngay.Text;
                    }
                    FlxDetail.Rows[i]["GioiTinh"] = reader["GioiTinh"];
                    FlxDetail.Rows[i]["DiaChi"] = reader["DiaChi"];
                    FlxDetail.Rows[i]["MaPB"] = reader["MaPB"];
                    FlxDetail.Rows[i]["Ma_BV"] = reader["Ma_BV"];
                    FlxDetail.Rows[i]["Ma_Tinh"] = reader["Ma_Tinh"];
                    FlxDetail.Rows[i]["NgayCap"] = DateTime.Today.ToShortDateString();
                    FlxDetail.Rows[i]["NgayNhan"] = DateTime.Today.ToShortDateString();
                    FlxDetail.Rows[i]["PS"] = reader["Maqt"];
                    FlxDetail.Rows[i]["MADT"] = reader["MaDt"];
                    FlxDetail.Rows[i]["HSL"] = reader["HSL"];
                    FlxDetail.Rows[i]["ML"] = reader["ML"];
                    stt++;
                    i++;
                }
                reader.Close();
            }
            btnThe.Enabled = false;
            btnChapnhan.Enabled = false;
            Cursor.Current = Cursors.Default;
        }
        /// <summary>
        /// tao the in the
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnChapnhan_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            string strKeygh = "", strKeysl = "", queryString = "";
            string strLoai, strMaTG;
            int sttINTHE_DTL = 0;
            int sttFlexGeneral = 0;
            FlxGeneral.Sort(SortFlags.Ascending, 2);
            FlxGeneral.Sort(SortFlags.Descending, 1);
            if (FlagGiahan == true)
            {
                strLoai = "1";
                strMaTG = "GH";
            }
            else
            {
                strLoai = "2";
                strMaTG = "T1";
            }

            using (SqlConnection connection =
               new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();

                for (int i = 1; i < FlxDetail.Rows.Count; i++)
                {
                    //duyet sang mot don vi khac
                    if (FlxDetail.Rows[i - 1]["Madvi"].ToString() != FlxDetail.Rows[i]["Madvi"].ToString())
                    {
                        strKeysl = ModuleBHXH.CreateRunninNo(rnd);//tao keysl
                        if (FlagGiahan == true)
                        {
                            strKeygh = ModuleBHXH.CreateRunninNo1(rnd); //tao keygh
                        }
                        else // truong hop tang moi lay keygh da co san
                        {

                            while (sttFlexGeneral < FlxGeneral.Rows.Count && FlxDetail.Rows[i]["Madvi"].ToString() != FlxGeneral.Rows[sttFlexGeneral]["Madvi"].ToString())
                            {
                                sttFlexGeneral++;

                            }
                            strKeygh = FlxGeneral.Rows[sttFlexGeneral]["KEYGH"].ToString();
                        }
                        // insert table INTHE_HDR
                        queryString = "exec sp_executesql N'INSERT INTO " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR (KEYSL,KEYGH,MADVI,NGAY,LOAI,TUNGAY,DENNGAY,MA_QUAN,USERCODE,GHICHU)"
                        + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)', N'@P1 char(20),@P2 char(8),@P3 char(7),@P4 smalldatetime,@P5 char(1),"
                        + " @P6 smalldatetime,@P7 smalldatetime,@P8 char(2),@P9 varchar(10),@P10 varchar(100)', "
                        + " '" + strKeysl + "', '" + strKeygh + "', '" + FlxDetail.Rows[i]["Madvi"].ToString() + "', '" + BHXH_NT.ModuleBHXH.strDate + "', '" + strLoai + "', '" + ModuleBHXH.FormatDate(Convert.ToDateTime(dtngay.Value)) + "', "
                        + " '" + string.Format("{0:00}", Convert.ToDateTime(dtDenngay.Value).Month) + "/" + Convert.ToDateTime(dtDenngay.Value).Day + "/" + Convert.ToDateTime(dtDenngay.Value).Year + "', '01', '" + ModuleBHXH.strUser + "', ''";
                        command.CommandText = queryString;
                        command.ExecuteNonQuery();
                        //lay max so the
                        sttINTHE_DTL = 0;
                        queryString = "exec sp_executesql N'SELECT TOP 1 STT FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_DTL WHERE KeyGH = @P1  ORDER BY STT DESC', N'@P1 varchar(8)', '" + strKeygh + "'";
                        command.CommandText = queryString;
                        SqlDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            sttINTHE_DTL = Convert.ToInt16(reader["STT"]);
                        }
                        reader.Close();
                    }
                    sttINTHE_DTL++;
                    //insert table INTHE_DTL
                    queryString = "exec sp_executesql N'INSERT INTO " + Settings.Default.DBName_Sothe + ".dbo.INTHE_DTL (KEYSL,KEYP,KEYGH,MADVI,LOAI,STT,SOBHXH,SOKCB,MADT,MATG,NGAYGIAO,"
                       + " NGAYMUA,TUNGAY,DENNGAY,MA_TINH,MA_BV,NGAYCAP,LANCAP,TINH_DEN,MAPB,GHICHU,USERCODE,MA_QUAN,HSL,ML) "
                       + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25)', "
                       + " N'@P1 char(20),@P2 char(8),@P3 char(8),@P4 char(7),@P5 char(1),@P6 numeric(6,0),@P7 char(16),@P8 char(16),@P9 char(2),"
                       + " @P10 char(2),@P11 smalldatetime,@P12 smalldatetime,@P13 smalldatetime,@P14 smalldatetime,@P15 char(2),@P16 char(3),"
                       + " @P17 smalldatetime,@P18 smallint,@P19 char(2),@P20 varchar(30),@P21 varchar(50),@P22 char(10),@P23 char(2),@P24 numeric(6,3),@P25 numeric(18,3)', "
                       + " '" + strKeysl + "', '" + ModuleBHXH.CreateRunninNo1(rnd) + "', '" + strKeygh + "', '" + FlxDetail.Rows[i]["MADVI"] + "', '" + strLoai + "', " + sttINTHE_DTL + " , '" + FlxDetail.Rows[i]["Sobhxh"] + "', '" + FlxDetail.Rows[i]["Sokcb"] + "', "
                       + " '" + FlxDetail.Rows[i]["MADT"] + "', '" + strMaTG + "',  '" + BHXH_NT.ModuleBHXH.strDate + "',  '" + BHXH_NT.ModuleBHXH.strDate + "', '" + ModuleBHXH.FormatDate(Convert.ToDateTime(FlxDetail.Rows[i]["TuNgay"])) + "',  '" + ModuleBHXH.FormatDate(Convert.ToDateTime(FlxDetail.Rows[i]["Denngay"])) + "', '" + FlxDetail.Rows[i]["MA_TINH"] + "',"
                       + " '" + FlxDetail.Rows[i]["MA_BV"] + "', '" + BHXH_NT.ModuleBHXH.strDate + "', 1, '', '" + FlxDetail.Rows[i]["MAPB"] + "', '" + FlxDetail.Rows[i]["PS"] + "', '" + ModuleBHXH.strUser + "', '01'," + BHXH_NT.ModuleBHXH.FormatNumner(FlxDetail.Rows[i]["HSL"]) + "," + BHXH_NT.ModuleBHXH.FormatNumner(FlxDetail.Rows[i]["ML"]) ;
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();

                }
            }
            FlxDetail.Rows.Count = 1;
            btnChapnhan.Enabled = false;
            Cursor.Current = Cursors.Default;
        }
        /// <summary>
        /// tao ma the vao vao table hscn
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnThe_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            string queryString = "";
            object objSobatdau;
            int intSobatdau;
            String strSokcb;
            //tao cac mang thong so cua cac don vi da chon
            ArrayList arrSobhxh = new ArrayList();
            lsDonvi = Condition.Split(',');
            lsMadt = strMadt.Split(',');
            lsMadvikcb = strMdvikcb.Split(',');
            lsMatinh = strMatinh.Split(',');
            lsMakhoikcb = strMakhoikcb.Split(',');
            for (int i = 0; i < lsDonvi.Length; i++) // duyet tung don vi
            {
                //lay cac sobhxh chua cap the cua don vi dang duyet
                queryString = "exec sp_executesql N'SELECT b.Sobhxh, SPACE(16) As Sokcb, b.Maqt, b.Pa,KeyGH  "
                   + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh  "
                   + " left join  " + Settings.Default.DBName_Sothe + ".DBO.INTHE_HDR f on f.Madvi = b.Madvi and f.Loai = ''1'' and denngay >= @P5,"
                   + "  (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi=@P1  AND LEFT(Maqt,6)=Denthang "
                   + "  AND LEFT(Maqt,6)<=@P2  AND CHARINDEX(Pa,''TT/TU/TN/GN/DN/CD'')=0 GROUP BY Sobhxh, Loaidt, Ma_Nte) a  "
                   + "  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND b.Madvi=@P3 "
                   + "  AND LEFT(b.Maqt,6)=b.Denthang AND CHARINDEX(b.Pa,''TT/TU/TN/GN/DN/CD'')=0  AND "
                   + "  (c.SoKCB='''' OR LEFT(c.SoKCB, 2)+SUBSTRING(c.SoKCB, 6, 5) <> @P4 ) AND "
                   + "  (b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 OR b.Pa=''TS'' ) ', "
                   + "  N'@P1 varchar(7),@P2 varchar(6),@P3 varchar(7),@P4 varchar(7),@P5 varchar(10)', "
                   + "  " + lsDonvi[i] + ", '" + Convert.ToDateTime(dtngay.Value).Year + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "', " + lsDonvi[i] + ",'" + lsMakhoikcb[i] + lsMadvikcb[i]
                   + "','" + string.Format("{0:00}", Convert.ToDateTime(dtngay.Value).Month) + "/" + Convert.ToDateTime(dtngay.Value).Day + "/" + Convert.ToDateTime(dtngay.Value).Year + "'";

                arrSobhxh.RemoveRange(0, arrSobhxh.Count);//xoa cac phan tu trong mang
                using (SqlConnection connection =
                new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = queryString;
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        arrSobhxh.Add(reader["Sobhxh"]);
                    }
                    reader.Close();
                    if (arrSobhxh.Count > 0)
                    {

                        //thuc hien sproc getsothe de update max so the kcb va lay so the bat dau
                        queryString = "exec sp_executesql N'EXEC GETSOTHE @P1 , @P2 ', N'@P1 varchar(7),@P2 float', " + lsDonvi[i] + "," + arrSobhxh.Count;
                        command.CommandText = queryString;
                        objSobatdau = command.ExecuteScalar();
                        intSobatdau = Convert.ToInt16(objSobatdau);
                        for (int j = 0; j < arrSobhxh.Count; j++)
                        {
                            //tao so the
                            strSokcb = lsMakhoikcb[i] + lsMadt[i].Trim() + Properties.Settings.Default.MaTinh + lsMadvikcb[i] + String.Format("{0:00000}", j + intSobatdau);
                            //update sothe vao table hosocn
                            queryString = "exec sp_executesql N'UPDATE dbo.HOSOCN SET SOKCB=@P1 WHERE SOBHXH=@P2', "
                                        + " N'@P1 varchar(16),@P2 char(16)', '" + strSokcb + "', '" + arrSobhxh[j] + "'";
                            command.CommandText = queryString;
                            command.ExecuteNonQuery();
                        }
                    }


                }

            }
            btnSolieu_Click(null, null);
            btnChapnhan.Enabled = true;

            Cursor.Current = Cursors.Default;
        }
        /// <summary>
        /// kiem tra cac dieu kien truoc khi tao so the
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnKiemtra_Click(object sender, EventArgs e)
        {
            btnThe.Enabled = false;

            for (int i = 1; i < FlxDetail.Rows.Count; i++)
            {
                if (FlxDetail.Rows[i]["Check"].ToString() == "True")
                {

                    if (FlxDetail.Rows[i]["MA_BV"].ToString().Trim() == "") { MessageBox.Show("NhËp m· BV"); return; }
                    if (FlxDetail.Rows[i]["PS"].ToString().Trim() == "") { MessageBox.Show("nhËp m· ®èi t­îng"); return; }
                    if (FlxDetail.Rows[i]["MA_TINH"].ToString().Trim() == "") { MessageBox.Show("nhËp m· TÜnh"); return; }

                    if (FlxDetail.Rows[i]["MA_BV"].ToString().Trim() == "008")
                    {
                        C1.Win.C1FlexGrid.CellStyle rs = this.FlxDetail.Styles.Add("rowcolor");
                        rs.ForeColor = Color.Red;
                        FlxDetail.Rows[i].Style = rs;
                    }
                    if (FlxDetail.Rows[i]["NGAYSINH"].ToString().Trim().Substring(0, 5) == "01/07")
                    {
                        C1.Win.C1FlexGrid.CellStyle rs = this.FlxDetail.Styles.Add("rowcolor");
                        rs.ForeColor = Color.Red;
                        FlxDetail.Rows[i].Style = rs;
                    }
                    if (FlxDetail.Rows[i]["MA_TINH"].ToString().Trim() == "" || FlxDetail.Rows[i]["MA_TINH"].ToString().Trim() == "56")
                    {
                        if (arrBV.FindIndex(x => x == FlxDetail.Rows[i]["MA_BV"].ToString().Trim()) == -1)
                        {
                            C1.Win.C1FlexGrid.CellStyle rs = this.FlxDetail.Styles.Add("rowcolor");
                            rs.ForeColor = Color.Red;
                            FlxDetail.Rows[i].Style = rs;
                            
                        }
                    }

                }
            }

            btnThe.Enabled = true;
        }

        private void btnSoPhieu_Click(object sender, EventArgs e)
        {
            FlxDetail.Rows.Count = 1;
            btnThe.Enabled = false;
            GetFlexGeneral(sender);
        }
        /// <summary>
        /// truong hop gia han
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btngiahan_Click(object sender, EventArgs e)
        {
            FlagGiahan = true;
            FlxDetail.Rows.Count = 1;
            GetFlexGeneral(sender);
        }
        /// <summary>
        /// truong hop bo sung
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnbosung_Click(object sender, EventArgs e)
        {
            FlagGiahan = false;
            FlxDetail.Rows.Count = 1;
            GetFlexGeneral(sender);
        }
        /// <summary>
        /// thay doi connectiontring
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cboInthe_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboInthe.SelectedIndex == 0)
            {
                connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
            }
            else if (cboInthe.SelectedIndex == 1)
            {
                connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString3;
            }
        }
        #endregion

        private void btnGiaHanNam_Click(object sender, EventArgs e)
        {
            btngiahan_Click(null, null);
        }

       
    }
}
